﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using Dapper;

namespace CodeRepositoryConsole.ORM.Dapper
{
    /**
     * see: https://www.jeremymorgan.com/blog/programming/how-to-dapper-web-api/
     */


    public class CustomerRespository : ICustomerRespository
    {
        private SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["OrmDemoConnection"].ConnectionString);

        /// <summary>
        /// Execute a query and map the results to a strongly typed List
        /// </summary>
        /// <param name="amount"></param>
        /// <param name="sort"></param>
        /// <returns></returns>
        public List<Customer> GetCustomers(int amount, string sort)
        {
            return conn.Query<Customer>("SELECT TOP {=count} [CustomerID],[CustomerFirstName],[CustomerLastName],[IsActive] FROM Customer ORDER BY CustomerID " + sort, new { count = amount }).ToList();
        }

        /// <summary>
        /// Execute a query and map it to a list of dynamic objects
        /// </summary>
        /// <param name="amount"></param>
        /// <param name="sort"></param>
        /// <returns></returns>
        public List<dynamic> GetCustomersDynamic(int amount, string sort)
        {
            return conn.Query("SELECT TOP {=count} [CustomerID],[CustomerFirstName],[CustomerLastName],[IsActive] FROM Customer ORDER BY CustomerID " + sort, new { count = amount }).ToList();
        }

        public Customer GetSingleCustomer(int customerId)
        {
            return conn.Query<Customer>("SELECT [CustomerID],[CustomerFirstName],[CustomerLastName],[IsActive] FROM [Customer] WHERE CustomerID=@CustomerID", new { CustomerID = customerId }).SingleOrDefault();          
        }

        public bool InsertCustomer(Customer ourCustomer)
        {
            int rowAffected = conn.Execute(@"INSERT INTO Customer ([CustomerFirstName],[CustomerLastName],[IsActive]) values (@CustomerFirstName, @CustomerLastName, @IsActive)", new { CustomerFirstName = ourCustomer.CustomerFirstName, CustomerLastName = ourCustomer.CustomerLastName, IsActive = true });

            if (rowAffected > 0)
            {
                return true;
            }

            return false;

        }

        public bool InsertCustomerRetId(Customer ourCustomer)
        {
            var sql = @"INSERT INTO Customer ([CustomerFirstName],[CustomerLastName],[IsActive]) values (@CustomerFirstName, @CustomerLastName, @IsActive); SELECT SCOPE_IDENTITY();";
            ourCustomer.CustomerID = conn.ExecuteScalar<int>(sql, ourCustomer);

            if (ourCustomer.CustomerID > 0)
            {
                return true;
            }

            return false;
        }

        public bool DeleteCustomer(int customerId)
        {
            int rowsAffected = conn.Execute(@"DELETE FROM Customer WHERE CustomerID=@CustomerID", new { CustomerID = customerId });

            if (rowsAffected > 0)
            {
                return true;
            }

            return false;
        }

        public bool UpdateCustomer(Customer ourCustomer)
        {
            int rowsAffected = conn.Execute("UPDATE [Customer] SET [CustomerFirstName]=@CustomerFirstName,[CustomerLastName] = @CustomerLastName, [IsActive] = @IsActive WHERE CustomerID =@CustomerID", ourCustomer);            

            if (rowsAffected > 0)
            {
                return true;
            }

            return false;
        }
    }
}
